<html>
<head>
	<title>Data Manager Tutorial</title>
</head>
<body>
	<h1>Data Manager Tutorial</h1>
		<p>
			The DataManager class exists to make your life easier as a programmer. It writes queries for programmers, so programmers never need to worry about the details of the database when they are programming database-driven applications. The page is a brief tutorial on using the data manager.
		</p>

	<h2>Step 1: Open the FSTDT project</h2>
		<p>
			Open Visual Web Developer. Make sure you <b>run as administrator</b>, otherwise you won't be able to step through code in debug mode.
		</p>
		<p>
		Open the FSTDT project from the project browser.
		</p>
	<h2>Step 2: Create A Table In the Database</h2>
	<p>
		Go to your Solution Explorer: <br />
		<img src="1.gif" />
	</p>
	<p>
		Click the Database Explorer tab:<br />
		<img src="2.gif" />		
	</p>
	<p>
		Open the tables folder in the database:<br />
		<img src="3.gif" />
	</p>
	<p>
		Right click on the table and choose "Add New Table:<br />
		<img src="4.gif" />
	</p>
	</p>
		A window to design your table will appear:<br />
		<img src="5.gif" />
	</p>
	
	<h2>Step 3: Design Your Table</h2>
	<p>
		The DataManager imposes a single restriction on the design of your database: <b>Every table must have 1 column called "ID". The column must be a "bigint", non-null, an identity column, and a primary key.</b> This is the only way DataManager can uniquely identify rows (as well, its the generally accepted way of uniquely identifying rows in a database anyway). Balls to those of you who like to create co-primary keys on a table, death to those who don't create primary keys at all.
	</p>
	<p>
		Create the required ID column:<br />
		<img src="6.gif" />
	</p>
	<p>
		Make some columns:<br />
		<img src="7.gif" />
	</p>
	<p>
		Take extreme consideration into the design of your tables. Good, well-organized tables can make the difference between a success and a disaster. SQL, in general, is not hard, but its easy to create crappy queries and poorly designed tables. SQL is said to be one of the few languages, like Lisp or Haskell, where programmers spend more time <i>thinking</i> than <i>writing code</i>.
	</p>
	<p>
		The table above has an interesting column called <code>ParentID</code>. ParentID is a foreign reference to the ID column in the same table, so this table has a 1-to-many relationship with itself. I've designed the table like this because I intend to create a simple hierarchy. The field is nullable because some nodes at the root of the hierarchy won't have a ParentID; I chose to use null, rather than 0, to indicate a root-level node, because a "0" is essentially a magic number chosen for convenience. Although I really don't like to have nulls in my database (because null is treated inconsistently in different databases), I'm more inclined to use a null rather than a magic number. On top of that, I can't arbitrarily choose "0" because I wouldn't be able enforce a foreign key constraint on the column; its best to allow nulls on this column.
	</p>
	<p>
		Save your table:<br />
		<img src="8.gif" />
	</p>

	<h3>Naming Conventions</h3>
	<p>
		At this point, I want to talk about naming conventions: I always group related tables together with a common prefix. Tables are always in the style of <code>prefix_tablename</code>. All the tables related to the Mainpage are prefixed with "fstdt_"; for example, I have a table called fstdt_quotes, fstdt_comments, fstdt_votes, and so on; all of the forum related tables are called yboard_posts, yboard_pms, yboard_users, yboard_forums, etc. I've chosen to use "tutorial_" as my prefix above.
	</p>
	<p>
		Table names are always plural nouns, such as "forums", "posts", "categories", "users", "articles", etc. A single row in a table is referred to in the singular: "forum", "post", "category", "user", "article".
	</p>
	<p>
		If you want to create a relationship between two tables, always use <code>tablenameID</code> to establish that two tables are related. For example, if I have a two tables, categories and articles, and I assume that articles has a many-to-one relationship with articles (i.e. each article is connected to one category, each category is connected to many articles), then I would design my tables as follows:

<pre>
        <u>Categories</u>
   ---> ID       <-------------
   `--- ParentID               |
        <i>other_columns</i>          |
                               |
        Articles               |
        ID                     |
        CategoryID  -----------
        <i>other_columns</i>
</pre>
		Articles.CategoryID points to Categories.ID. Categories.ParentID points to Categories.ID.
	</p>
	<p>
		Admittedly, ParentID is an exception to naming convention rule, since the field refers to another record in the same table. The name ParentID conveys the relationship between categories more intuitively than the name CategoryID (which could easily be confused for a primary key).
	</p>

	<h2>Step 4: Add Some Dummy Data</h2>
	<p>
		Create dummy data in the new table.
	</p>
	<p>
		Right click on the table and choose "Show Table Data"<br />
		<img src="9.gif" />
	</p>
	<p>
		A new window will be showing. Enter some data directly into your table by typing directly in that window:<br />
		<img src="10.gif" />
		
	</p>
	<p>
		The table above is designed as a hierarchy which looks like this:
<pre>
- Awesome Table
- Religion and Philosophy
  - Christianity
  - Animal Rights
- Lounge
- Computers and Such
  - Computer Programming
    - C#
    - F# / OCaml
    - Web Development
</pre>
	</p>
	<p>
		The hiearachy is determined by the ParentID field, not the order that I entered data. (Incidentally, I entered data in exactly the order that they appear in the hierarchy.)
	</p>	

	<h2>Step 5: Make a Data Manager Class</h2>
	<p>
		You will have two data manager classes for every one table in your database. One class is a wrapper for the table, which gives you access to the rows in your table; the other class is a wrapper for each row, which gives you access to each field in the row.
	</p>
	<p>
		The row class descends an abstract class called <code>DAL.DataManagerRow</code>. This class only has one property called ID. The ID property directly corresponds to the ID field in the database.
	</p>
	<p>
		The table class descends from <code>DAL.DataManager&lt;DataManagerRow&gt;</code>, which is a generic, abstract class. This class has three must-override methods:
	</p>
	<ol>
		<li><code>public string TableName{ get; }</code>. Returns the name of the table.</li>
		<li><code>protected void CopyToItem(DataRow Row, RowType Item)</code>. Copies data from the datamanager into row objects, which is used for sql SELECTs.</li>
		<li><code>protected abstract void CopyFromItem(RowType Item)</code>. Copies data from a row object back into the datamanager, which is used for sql INSERTs and UPDATEs.</li>
	</ol>
	<h2>Using the Data Manager Helper</h2>
	<p>
		It can be cumbersome and repetitive to create these classes, especially if the table has a lot of fields. Use the NUnit Helper to automatically generate these classes for you. First, open the NUnitHelper.exe program:<br />
		<img src="11.gif" />
	</p>
	<p>
		Click on the DataManager button on the lower lefthand side of the window. A new window will pop up:<br />
		<img src="12.gif" />

	</p>
	<p>
		Navigate to the folder where your copy of FSTDT is located. The database is stored in the App_Data directory, its called fstdt_db.mdf:<br />
		<img src="13.gif" />
	</p>
	<p>
		Open the file. If you get this error<br />
		<img src="14.gif" />
	<p>
	<p>
		Then it means you are still actively connected to your database. Go back to Visual Web Developerhave an active connection to your database, open your Data Base Exporer Window, right click on your database, then choose "Close Connection<br />
		<img src="15.gif" />
	</p>
	<p>
		You should be able to open the file now.
	</p>
	<p>
		The Data Manager Helper will populate the list box with the names of every table in the database<br />
		<img src="16.gif" />
	</p>
	<p>
		Click on your table. This will automagically generate the classes for your data manager object:<br />
		<img src="17.gif" />
	</p>
	<p>
		Right click on the text and copy it to your clipboard.
	</p>
	<h3>How the Data Manager Helper Works</h3>
	<p>
		The NUnit helper creates data manager classes based on certain assumptions about the fields in a table:
	</p>
	<ol>
		<li>
			<p>
				Your table class is always a plural word. Your row class is always the singular form of that word. If you use the Data Manager helper to generate classes from a table called Users, it will create a table class called Users, and a row class called User.
			</p>
			<p>
				If you followed the example above, you'll noticed that the Data Manager helper created a table class called "test_Categories", and a row class called "test_Category".
			</p>
		</li>
		<li>
			<p>
				Properties in your row class correspond directly to fields the table. Properties are created with the following rules:
				<ol>
					<li>
						<p>
							Sql datatypes will be converted into C# equivalent datatypes. Varchar, Char, NChar, NVarchar become string. Smalldatetime, DateTime become DateTime. int becomes int (no kidding!). Bigint becomes Int64. Etc.
						</p>
					</li>
					<li>
						<p>
							In the special case that your sql datatype is nullable, the data manager helper will create nullable properties. A nullable smalldatetime will become Nullable&lt;DateTime&gt;, a nullable int will become Nullable&lt;int&gt;, etc. Strings are not converted in this way, because they are nullable by default.
						</p>
					</li>
					<li>
						<p>
							A field name that ends with "ID" is assumed to be another data manager object. For example, if you have a field called UserID, you'll get a property which references a User object.
						</p>
						<p>
							In the example above, you'll notice that there is a field called "ParentID". Data manager helper isn't smart to enough to assume that ParentID is a reference to the same table; it thinks its a refence to another data manager object called Parent. So it actually created a property like this:
<pre>
        private Parent _Parent;
        public Parent Parent
        {
            get
            {
                return _Parent;
            }
            set
            {
                _Parent = value;
            }
        }
</pre>
						</p>
						<p>
							You'll need to fix that property, so that it refences the Category class, rather than a non-existent "Parent" class.
						</p>
					</li>
				</ol>
			</p>
		</li>
	</ol>
	
	<h2>Step 5.2: Adding Your Auto-generated class to Visual Web Developer</h2>
	<p>
		Go back to Visual Web Developer. Open your solution explorer, right click on your App_Code/csharp directory, and create a folder called ForumManager:<br />
		<img src="18.gif" /><br />
		<img src="19.gif" /><br />
		<img src="20.gif" />		
	</p>
	<p>
		Right click on the new folder and click "Add New Item":<br />
		<img src="21.gif" />
	</p>
	</p>
		Choose the "class" icon, and name your class "Categories.cs":<br />
		<img src="22.gif" />
	</p>
	<p>
		Visual Web Developer will create a new file for you. Replace the entire contents of the file with the contents of your clipboard by hitting Ctrl-A, followed by Shift-Insert (or Ctrl-V).<br />
		<img src="23.gif" />
	</p>
		
	<p>
		You probably won't like the default names that data manager helper chose for your classes. To fix those, use the Refactor tools. Right click on any instance of the word "tutorial_categories", and click Refactor -&gt; Rename: <br />
		<img src="24.gif" />
	</p>
	<p>
		A dialog will pop up asking you what you want to rename the field. Type in "Categories":<br />
		<img src="25.gif" />
	
	</p>
	<p>
		Hit ok, and all instances of "test_Categories" will be automagically changed to "Categories":<br />
		<img src="26.gif" />
	</p>
	<p>
		Repeat the same process on the word "test_Category", change it to "Category":<br />
		<img src="27.gif" />
	</p>
	<p>
		Due to a nuance with Data Manager helper, it created some properties with references to non-existence "Parent" and "Parents" classes. You can't use the Refactor tools on those since those classes don't exist, you'll have to edit the file by hand. Fortunately, you only need to edit two sections: The Categories.CopyToItem method, and the Category.Parent property.
	</p>
	<p>
		In Categories.CopyToItem, line 34 references a non-existent "Parents" class. Change "Parents" to "Categories".<br />
		<img src="28.gif" /><br />
		<img src="29.gif" />
	</p>
	<p>
		Likewise, edit the Category.Parent property. Change the datatype from "Parent" to "Category":<br />
		<img src="30.gif" /><br />
		<img src="31.gif" />
	</p>
	<p>
		You should be able to build and compile your project without errors now.
	</p>

	<h3>How The Data Manager Works</h3>
	<p>
		Now that your project compiles, take the time to examine the classes you've created. The Categories classes directly references the name of the table in the database and the names of columns in the table. When the datamanager builds queries, it will use the values in the Categories class to construct the query.
	</p>
	<p>
		Notice that the Categories class is defined as follows:<br />
<pre>
	public class Categories : DAL.DataManager&lt;Category&gt;
</pre>
	</p>
	<p>
		The Categories class inherits all the properties and methods of the DataManager. The <code>&lt;Category&gt;</code> part is the C# syntax for a <a href="http://msdn2.microsoft.com/en-us/library/ms379564(VS.80).aspx">generic class</a>. A generic class is basically a class that uses placeholder datatypes instead of concrete datatypes; when I create an instance of my generic class, I fill in the place holder datatype with my own datatype, which allows me to reuse the same class for any datatype without compromising type-safety or performance. For the purposes of the data manager, the <code>&lt;Category&gt;</code> explicitly ties the Category row class to my Categories table class.
	</p>
	<p>
		To put it another way, the Categories class can <i>only</i> return Category objects or lists of Category objects. Of course, you can't tie just any two classes together with the data manager. You can only tie a row class to a table class if, and only if, the row class descends from DAL.DataManagerRow. (That is <i>not</i> a bad thing. That is a <i>good</i> thing. I deliberately designed the data manager that way because it guarantee's type safety.)
	</p>
	<p>
		The DataManager has a lot of methods. You'll become most familiar with two methods, <code>GetItems()</code> and <code>GetItem(long id)</code>. Whenever you make a call to one of these methods, the data manager builds and executes a SELECT statement behind the scenes.
	</p>
	<p>
		For each row in the dataset, it creates a new instance of a row class (the Category class in this case), it calls CopyToItem(DataRow Row, DataManagerRow Item) to initialize the properties in the row class, then the newly initialized row class is appended to a List&lt;DataManagerRow&gt;.
	</p>
	<p>
		As you might have guessed, its easy to tie two tables together. If you have two table classes, Articles and Users, and you want to associate each Article with a User, you would create a property in the Article row class like this:
<pre>
	private User _user;
	public User User
	{
		get { return _user; }
		set { _user = value; }
	}
</pre>
		In the CopyToItem method, you could fill that user by calling
<pre>
	protected void CopyToItem(DataRow Row, Article Item)
	{
		Item.User = Users.Item(Convert.ToInt64(Row["UserID"]));
		// initialize other fields
	}

	protected void CopyFromItem(Article Item)
	{
		AddParam("UserID", Item.User.ID);
		// add other fields
	}
</pre>
	</p>
	<p>
				Behind the scenes, if you are calling other <code>SomeTable.Item(id);</code> in the CopyToItem method, then you will be executing an additional query against the database for every row. Although this sounds like a performance issue, believe me, its not a bad as you think. First, since the query uses the ID property, which is the primary key field in the Users table, which is basically the optimum way to retrive any field from a table. Second, whenever an object is created in the database, it's stored in an object pool; an object only gets created once, and its reused from the pool everytime afterward. If you call Users.Item(15) consequetively 80 times, you'll execute 1 query against the database the first time you request that item.
	</p>
	<p>
		When you call GetItems(), the data manager will return a List&lt;rowclass&gt;. You can modify the results of your query by calling the <code>AddParam(string columnName, object value)</code>, and <code>AddSort(string colname, Sort.Direction)</code> methods. These modifiers add where clauses and order by clauses to your SQL query. If you wanted to retrieve all of the Categories with a particular ParentID, then you make a function like this:
<pre>public static List&lt;Category&gt; GetCategoriesByParentID(long parentID)
{
	Instance.AddParam("ParentID", parentID);
	Instance.AddSort("Name", Sort.Asc);
	return GetItems();
}
</pre>
	</p>	
	<p>
		(You have to use Instance.AddParam and Instance.AddSort since the method is static.)
	</p>
	<p>
	AddParam also has an overloaded method called <code>AddParam(colname string, Comparison comparison, object value)</code> which allows you to change the properties of the where clause. Comparison is an enum, which has value Equal, GreaterThan, LessThanEqual, and a few othe common comparison operators. Lets say you wanted to search all categories that contained a specific string in the description:
<pre>public static List&lt;Category&gt; GetCategoriesByDescription(string description)
{
	Instance.AddParam("Description", Comparison.Like, "%" + description + "%");
	Instance.AddSort("Name", Sort.Asc);
	return GetItems();
}
</pre>
	</p>
	<p>
		Don't worry about the <code>"%" + description + "%"</code>. All queries are built up as parameterized queries behind the scenes, so there's no risk of <a href="http://www.unixwiz.net/techtips/sql-injection.html">SQL injection</a>.
	</p>

	<h2>Step 6: Bind Data To A Grid</h2>
	<p>
	Most of the time, you return <code>List&lt;rowclass&gt;</code>s with the DataManager. Since Lists inherit from IEnumerable, you can databind to a grid in exactly the same way as you do with all other datasources.
	</p>
	<p>
		As a brief demonstration of how you could use the Data Manager, I'll just demonstrate how to make a parent-child relationship using the Categories class that was already created.
	</p>
	<p>
		In the categories class, I've added two new methods:
<pre>
	public static List&lt;Category&gt; GetBreadCrumbByID(long id)
        {
            // takes in a ID to get a category. Then a list of categories is built up one by 
            // one by iterating through the Parent objects.
            List&lt;Category&gt; myList = new List&lt;Category&gt;();

            Category currentCategory = Instance.GetItem(id);
            while (currentCategory != null)
            {
                myList.Add(currentCategory);

                // move up the hierarchy. w00t for tail recursion
                currentCategory = currentCategory.Parent;
            }

            // the list is creating from leaf to root, but I want to display the list as
            // root to leaf, so I'll reverse it:
            myList.Reverse();
            return myList;
        }

        public static List&lt;Category&gt; GetByParent(Category Parent)
        {
            // get the child categories of a particular category
            if (Parent != null)
            {
                Instance.AddParam("ParentID", Parent.ID);
            }
            else
            {
                Instance.AddParam("ParentID", System.DBNull.Value);
            }
            Instance.AddSort("Name", Sort.Asc);
            return Instance.GetItems();
        }
</pre>
	</p>

	<p>
Here's the aspx page:
<pre>
&lt;%@ Page Language="C#" %&gt;

&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;

&lt;script runat="server"&gt;
    
    private void Page_Load()
    {
        if (!Page.IsPostBack)
        {
            UpdateChildren(null);
        }
    }

    private void ShowRoot(object sender, CommandEventArgs e)
    {
        UpdateChildren(null);
    }
    
    private void Rebind(object sender, CommandEventArgs e)
    {
        UpdateChildren(DAL.Categories.Item(Convert.ToInt64(e.CommandArgument)));
    }

    private void UpdateChildren(DAL.Category Parent)
    {
        gridChild.DataSource = DAL.Categories.GetByParent(Parent);
        gridChild.DataBind();
        
        if (Parent != null)
        {
            rptBreadcrumb.DataSource = DAL.Categories.GetBreadCrumbByID(Parent.ID);
        }
        rptBreadcrumb.DataBind();
    }
&lt;/script&gt;

&lt;html xmlns="http://www.w3.org/1999/xhtml" &gt;
&lt;head runat="server"&gt;
    &lt;title&gt;Untitled Page&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
    &lt;form id="form1" runat="server"&gt;
        &lt;div&gt;
            &lt;asp:Repeater runat="server" ID="rptBreadcrumb"&gt;
                &lt;HeaderTemplate&gt;
                    &lt;asp:LinkButton runat="server" ID="lnkRoot" OnCommand="ShowRoot"&gt;Root&lt;/asp:LinkButton&gt;
                    ::
                &lt;/HeaderTemplate&gt;
                &lt;ItemTemplate&gt;
                    &lt;asp:LinkButton runat="server" ID="lnkBreadcrumb"
                        Text='&lt;%# Eval("Name") %&gt;'
                                OnCommand="Rebind"
                                CommandArgument='&lt;%# Eval("ID") %&gt;'/&gt;
                &lt;/ItemTemplate&gt;
                &lt;SeparatorTemplate&gt;
                    ::
                &lt;/SeparatorTemplate&gt;
            &lt;/asp:Repeater&gt;
        &lt;/div&gt;
        &lt;div&gt;
            &lt;asp:GridView runat="server" ID="gridChild"
                AutoGenerateColumns="false" HorizontalAlign="left"
                Width="40%" ShowHeader="false"&gt;
                &lt;Columns&gt;
                    &lt;asp:TemplateField&gt;
                        &lt;ItemTemplate&gt;
                            &lt;asp:LinkButton ID="lnkChild" runat="server"
                                Text='&lt;%# Eval("Name") %&gt;'
                                OnCommand="Rebind"
                                CommandArgument='&lt;%# Eval("ID") %&gt;' /&gt;
                        &lt;/ItemTemplate&gt;
                    &lt;/asp:TemplateField&gt;
                &lt;/Columns&gt;
                &lt;EmptyDataTemplate&gt;
                    End of the line.
                &lt;/EmptyDataTemplate&gt;
            &lt;/asp:GridView&gt;
        &lt;/div&gt;
    &lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
</pre>
	</p>
	<p>
		The example above looks more complex than it really is. Ignoring the ASP.Net markup, the hierarchy + breadcrumb implementation totals around 20 lines of code, and most of it is null handling. I was able to do all of that in about 5 minutes without writing a single query at all.
	</p>
	<h2>Done!</h2>
	<p>
		In most applications, data manager logic will be simpler than the example demostrated above. Most databinding is going to simple, usually something like <code>someGrid.DataSource = Articles.Item(5);</code>, or something equally lame.
	</p>
	<p>
		I want to use the DataManager as much as possible on FSTDT, and I want to ween people off of their dependence on stored procedures / embedded sql. DataManager provides a standard, simple way to access data.
	</p>
	
	<h2>Other Things You Can Do With Datamanager</h2>
	<h3>Inserts</h3>
	<p>
		If you want to add a new item to a table, create a new instance of that item and pass it to the table class's Add() method. Here's how you make a new category:
<pre>
	Category myCategory = new Category();
	myCategory.Name = "Yahweh!!";

	Categories.Add(myCategory);
</pre>
	When you create a <code>myCategory</code>, its <code>ID</code> property is 0. After you call the Add() method, <code>myCategory.ID</code> is equal to its ID in the database.
	</p>

	<h3>Updates</h3>
	<p>
		You can edit an item in a similar way. Retrieve the item, modify its properties, pass the item into the table class's Save() method.
<pre>
	Category myCategory = Categories.Item(10);
	myCategory.Name = "ZOMG!" // changing the category's name

	Categories.Save(mYCategory); // changes committed to database
</pre>
	</p>
	<p>
		You can only edit a single item at a time. If you need to modify a group of item, return the items as a list, loop through the list, modify each item individually.
	</p>
	
	<h3>Deletes</h3>
	<p>
		I've not added a method for deleting. I don't want people deleting anything out of the database, that's just asking for trouble because an accidental deletion can't be undone.
	</p>

	<h3>Counts</h3>
	<p>
		When you just want a count of items instead of a full-blown list from the database, Datamanager has <b>int GetCount()</b> to return a count of items which match given criteria. GetCount() is used in exactly the same way as the GetItems() method.
	</p>
</body>
</html>